This RMarkdown loads, clean and prepare data for further analysis
From: https://www.hcup-us.ahrq.gov/reports/trendtables/summarytrendtables.jsp#export.
This Data Set contains the daily summary files with one record for each monitor that reported data for the given day
After Loading, we aggregate AQI US County Data by Month and per Year in order to prepare this data to merge with “HOspital Visits & Emergencies” Data Set from 2017 to 2020
A caption
Each NAAQS pollutant has a separate AQI scale, with an AQI rating of 100 corresponding to the concentration of the Federal Standard for that pollutant.
And this is the tibble with AQI Data aggregated by month:
head(aggtot)
## # A tibble: 6 x 8
## State.Name county.Name yearmm Cat Category AQI Def_parm aggnum
## <chr> <chr> <date> <dbl> <chr> <int> <chr> <int>
## 1 Alabama Baldwin 2017-01-01 1 Good 30 PM2.5 1
## 2 Alabama Baldwin 2017-02-01 1 Good 38 PM2.5 1
## 3 Alabama Baldwin 2017-03-01 2 Moderate 58 PM2.5 2
## 4 Alabama Baldwin 2017-04-01 2 Moderate 74 Ozone 1
## 5 Alabama Baldwin 2017-05-01 3 Unhealthy for S~ 108 Ozone 1
## 6 Alabama Baldwin 2017-06-01 2 Moderate 65 PM2.5 1
Next step is to merge AQI Data with US Counties geographical data with the objective to plot a US map with those values
–
In the map below we can see that the data collected is really useful for our purposes. The map represent Air Quality Indexes For the 2017 to 2020 years:
From: https://www.hcup-us.ahrq.gov/reports/trendtables/summarytrendtables.jsp#export
This is the data for the Hospital Visits and Emergencies in US States from 2017 to 2020
This data is an excel report and the different States date are in different sheets of the same excel workbook
We have merged geographical US states data with the Hospital visits dataset to generate the animated map below
From: https://www2.census.gov/programs-surveys/popest/datasets/2010-2020/state/totals/USA
With this data we can calculate the rates for Hospital Visits & Emergencies per Population in each US State
## # A tibble: 6 x 6
## REGION DIVISION STATE NAME POPULATION YEAR
## <chr> <chr> <int> <chr> <int> <dbl>
## 1 0 0 0 United States 325122128 2017
## 2 0 0 0 United States 326838199 2018
## 3 0 0 0 United States 328329953 2019
## 4 0 0 0 United States 329484123 2020
## 5 1 0 0 Northeast Region 56083383 2017
## 6 1 0 0 Northeast Region 56084543 2018